package cn.soft863.users.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import cn.soft863.common.util.DbUtil;
import cn.soft863.common.util.PageInfoUtil;
import cn.soft863.users.dao.IAConsumeHistoryDao;
import cn.soft863.users.pojo.AConsumeHistory;


/**
 * 消费表的Dao
 * 
 * @author 902-B-Book
 *
 */
public class AConsumeHistoryDaoImpl implements IAConsumeHistoryDao
{
	/**
	 * 将数据库中取出的记录一一的赋值给java的pojo对象
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private AConsumeHistory proccedPojo(ResultSet rs) throws SQLException
	{
		AConsumeHistory consume = new AConsumeHistory() ; 
		consume.setId(rs.getInt("id"));
		consume.setBalanceId(rs.getInt("balanceId"));
		consume.setOrdersId(rs.getInt("ordersId"));
		consume.setAccountId(rs.getInt("accountId"));
		consume.setThirdSn(rs.getString("thirdSn"));
		consume.setConsumeTime(rs.getInt("consumeTime"));
		consume.setPrice(rs.getDouble("price"));
		consume.setBhType(rs.getByte("bhType"));
		consume.setStatus(rs.getByte("status"));
		consume.setCreateTime(rs.getTimestamp("createTime"));
		consume.setUpdateTime(rs.getTimestamp("updateTime"));
		consume.setPubTime(rs.getTimestamp("pubTime"));

		return consume;
	}
	
/*保存一条消费记录*/
	@Override
	public int saveOneDao(AConsumeHistory consume)
	{
		String sql = "insert into a_consume_history (balanceId, ordersId, accountId, thirdSn,consumeTime,price,bhType, status, createTime, updateTime, pubTime) "
				+ "values "
				+ "(?,?,?,?,?,?,?,?,?,?,?)" ; 
		/* 添加操作JDBC四步走 */
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null ; 
		ResultSet rs = null ; 
		try
		{
			/* 返回主键 */
			ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			/* 为占位符?赋值 */
			ps.setObject(1, consume.getBalanceId());
			ps.setObject(2, consume.getOrdersId());
			ps.setObject(3, consume.getAccountId());
			ps.setObject(4, consume.getThirdSn());
			ps.setObject(5, consume.getConsumeTime());
			ps.setObject(6, consume.getPrice());
			ps.setObject(7, consume.getBhType());
			ps.setObject(8, consume.getStatus());
			ps.setObject(9, consume.getCreateTime());
			ps.setObject(10, consume.getUpdateTime());
			ps.setObject(11, consume.getPubTime());
			
			int res = ps.executeUpdate();
			/* 获取主键 */
			rs = ps.getGeneratedKeys();
			if(rs.next())
			{
				int genKeys = rs.getInt(1);
				consume.setId(genKeys);
			}
			return res ;
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			DbUtil.closeAll(rs, ps, con);
		}
		return 0;
	}

	@Override
	public int updateOneDao(AConsumeHistory consume)
	{
		String sql = "update a_consume_history set balanceId=?, ordersId=?, accountId=?, thirdSn=?, consumeTime=?, price=?, bhType=?,"
				+ "status=?, updateTime=?  WHERE (id=?)"; 
		/* 创建一个链接对象 */
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ; 
		
		int res = 0 ; 
		/* 创建一个语句对象 */
		try
		{
			ps = con.prepareStatement(sql);
			/* 设置占位符 */
			ps.setObject(1, consume.getBalanceId());
			ps.setObject(2, consume.getOrdersId());
			ps.setObject(3, consume.getAccountId());
			ps.setObject(4, consume.getThirdSn());
			ps.setObject(5, consume.getConsumeTime());
			ps.setObject(6, consume.getPrice());
			ps.setObject(7, consume.getBhType());
			ps.setObject(8, consume.getStatus());
			ps.setObject(9, consume.getUpdateTime());
			ps.setObject(10, consume.getId());
			res = ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			/* 关闭链接 */
			DbUtil.closeAll(rs, ps, con);
		}
		return res;
	}
	/**
	 * 既要支持分页,也要支持不分页
	 * 
	 * 查询分页的时候应该先查询总条数,然后再分页
	 */
	@Override
	public List<AConsumeHistory> findCondListDao(PageInfoUtil pageInfoUtil, Map<String, Object> condMap)
	{
		// TODO Auto-generated method stub
		List<Object> paramList = new ArrayList<Object>();
		/**
		 * 如果不分页,sql语句里面木有?
		 * 如果分页,sql语句里面有?
		 * 
		 * 在给?设置值的时候只能在ps生成以后才可以.有的时候有问可以设置,有的时候木有?不能设置
		 * 咋办呢??的下标值.
		 */
		String sql = "select * from a_consume_history where 1 = 1 "; 
		
		/* 按照关键字查询 
		 * 键:是为了区分不同的搜索条件
		 * 值:就是搜索条件
		 * */
		if(condMap.get("keyword") != null && !"".equalsIgnoreCase(condMap.get("keyword") + ""))
		{
			/*第三方流水号模糊查询*/
			sql += " and (thirdSn like ? ) ";
			paramList.add("%" + condMap.get("keyword") + "%");
			/*
			 * select * from a_consume email like ? or phone like ?
			 *  */
		} 
		/* 按照状态搜索 */
		if(condMap.get("status") != null && !"".equalsIgnoreCase(condMap.get("status") + ""))
		{
			sql += " and status = ? ";
			paramList.add(condMap.get("status"));
			/*
			 * select * from a_consume status = ?
			 *  */
		} 
		/* 按照时间搜索 */
		if(condMap.get("stDate") != null && !"".equalsIgnoreCase(condMap.get("edDate") + ""))
		{
			sql += " and (createTime >= ? and createTime < ? )";
			paramList.add(condMap.get("stDate"));
			paramList.add(condMap.get("edDate"));
			/*
			 * select * from a_consume status = ?
			 *  */
		}
		/*
		 * select * from a_consume where 1 = 1 and email like ? or phone like ?
		 * select * from a_consume where 1 = 1 and status = ?
		 * select * from a_consume where 1 =1  and (email like ? or phone like ?) and  status = ?
		 * 
		 * */
		
		/* 创建一个链接对象 */
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ; 
		
		List<AConsumeHistory> consumeList = new ArrayList();
		int res = 0 ; 
		/* 创建一个语句对象 */
		try
		{
			if(pageInfoUtil != null)
			{
				/*----------------查询总条数------------------*/
				/* 原始的SQL语句:select * from t where (email like ? and trueName like ?) and status = ? and createTime >= ? and createTime < ? 
				 * 计算总记录数:select count(*) from t where (email like ? and trueName like ?) and status = ? and createTime >= ? and createTime < ?
				 * 分页:select * from t where (email like ? and trueName like ?) and status = ? and createTime >= ? and createTime < ? limit ? , ? 
				 *  */
				System.out.println("-----原始的SQL语句-----" + sql);
				String fromIndex = "from" ;
				String countSQL = "select count(*) " + sql.substring(sql.indexOf(fromIndex) , sql.length()); 
				System.out.println("-----countSQL-----" + countSQL);
				ps = con.prepareStatement(countSQL);
				/* 专门解决?的顺序和是否有?的问题?(占位符) */
				int count = 1 ; 
				for (Iterator iterator = paramList.iterator(); iterator.hasNext();)
				{
					Object obj = (Object) iterator.next();
					ps.setObject(count, obj);
					count ++ ; 
				}
				rs = ps.executeQuery();
				/* 凡是count(*)查询的结果只有一条 */
				if(rs.next())
				{
					int totalRecord = rs.getInt(1);
					/* 分页的工具类天天盼着呢 */
					pageInfoUtil.setTotalRecord(totalRecord);
				}
				
				/*----------------分页查询数据------------------*/
				/* 使用的是limit
				 * ?:起始条数
				 * ?:往后查询多少条
				 *  */
				sql += " limit ? , ? ";
				paramList.add(pageInfoUtil.getCurrRecord());
				paramList.add(pageInfoUtil.getPageSize());
				System.out.println("-----分页取数据的SQL语句-----" + sql);
				ps.close();
				rs.close();
			}
			
			ps = con.prepareStatement(sql);
			/* 专门解决?的顺序和是否有?的问题?(占位符) */
			int count = 1 ; 
			for (Iterator iterator = paramList.iterator(); iterator.hasNext();)
			{
				Object obj = (Object) iterator.next();
				ps.setObject(count, obj);
				count ++ ; 
			}
			rs = ps.executeQuery() ;
			/* sql返回的结果有几条
			 *  */
			while(rs.next())
			{
				AConsumeHistory consume = proccedPojo(rs);
				
				/* 将数据库查询出来的记录直接放到集合中 */
				consumeList.add(consume);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			/* 关闭链接 */
			DbUtil.closeAll(rs, ps, con);
		}
		return consumeList;
	}
/*查找一条记录*/
	@Override
	public AConsumeHistory findOneDao(Map<String, Object> condMap)
	{
		// TODO Auto-generated method stub
		List<Object> paramList = new ArrayList<Object>();
		/**
		 * 如果不分页,sql语句里面木有?
		 * 如果分页,sql语句里面有?
		 * 
		 * 在给?设置值的时候只能在ps生成以后才可以.有的时候有问可以设置,有的时候木有?不能设置
		 * 咋办呢??的下标值.
		 */
		String sql = "select * from a_consume_history where 1 = 1 "; 
		
		/* 按照id查询
		 * dao中取,在使用调用的时候放
		 * */
		if(condMap.get("id") != null && !"".equalsIgnoreCase(condMap.get("id") + ""))
		{
			sql += " and id = ? ";
			paramList.add(condMap.get("id"));
		}
		
		/* 创建一个链接对象 */
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ; 
		
		List<AConsumeHistory> consumeList = new ArrayList();
		int res = 0 ; 
		/* 创建一个语句对象 */
		try
		{
			System.out.println("sql---------" + sql);
			ps = con.prepareStatement(sql);
			/* 专门解决?的顺序和是否有?的问题?(占位符) */
			int count = 1 ; 
			for (Iterator iterator = paramList.iterator(); iterator.hasNext();)
			{
				Object obj = (Object) iterator.next();
				ps.setObject(count, obj);
				count ++ ; 
			}
			rs = ps.executeQuery() ;
			/* sql返回的结果有几条
			 * 通过id查询的时候始终只有一条
			 *  */
			if(rs.next())
			{
				/*
				 * 将数据库里面查询出来的信息一一的赋值给java中的POJO
				 *  */
				AConsumeHistory consume = proccedPojo(rs);
				return consume ; 
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			/* 关闭链接 */
			DbUtil.closeAll(rs, ps, con);
		}
		return null;
	}

}
